Generated code - Linq to LLBLGen Pro, Function mappings
Preface
A new facility offered by Linq, and thus also by Linq to LLBLGen Pro, is the ability to specify a call to a .NET method or property which is automatically transformed to a database function call or database construct which produces the same result. This section describes this feature in depth, how you can define your own mappings with little effort and also how this facility can be used to enable Full text search on for example SQL Server but also other databases which support Full text search like MySql.
Linq to LLBLGen Pro comes with a large set of pre-mapped functions / database constructs out of the box. These mappings are described briefly in this section. Not all constructs could be re-created on all databases. For example due to its very limited SQL language, it wasn't possible to implement a lot of the .NET methods
in SQL on Firebird.
Not only class methods can be mapped to a database construct, also properties can be mapped onto database constructs. Per database the pre-defined mappings are created in the DQE of that particular database. All mappings result in a
DbFunctionCall element in the final query.
A function mapping is always between a .NET method or property and a database function or construct. This means that if you have a function defined in your database schema and you want to call it inside a Linq query you can do so by defining a mapping between a .NET method and that database function. If such a .NET method doesn't exist, you
have to define one yourself. This method can be empty, as it's not used at runtime. This section contains an example of this where a .NET method is mapped onto a user defined database function.
FunctionMapping and FunctionMappingStore
A mapping between a .NET method or property is defined by creating a
FunctionMapping instance and storing it in a
FunctionMappingStore instance. Every DQE has a default set of
FunctionMapping
instances which are stored in the DQE's default
FunctionMappingStore. This
FunctionMappingStore is obtained automatically by the Linq to LLBLGen Pro provider so you can write a Linq query and use the pre-defined mappings right away.
You can extend this system by specifying your own
FunctionMapping instances in a custom
FunctionMappingStore instance. Custom mappings take precedence over the pre-defined mappings in the DQE, so if you want to change the pre-defined mappings, you can easily do so by simply specifying a new mapping in a custom
FunctionMappingStore instance.
A
FunctionMapping instance has a
Key and a
Pattern. The
Key
is composed of the following:
Method name|
Defining type name|
Number of parameters. The
Pattern is a pattern which can be used with a
DbFunctionCall, e.g.: "{0} + {1}". The FunctionMapping constructor makes it easy to define a function mapping. For example, to define a database mapping for String.Concat, which accepts 2 parameters, and which should result in
operand1 +
operand2 in
SQL Server, one can use the following
FunctionMapping construction:
FunctionMapping stringConcatMapping = new FunctionMapping(typeof(string), "Concat", 2, "({0} + {1})");
Dim stringConcatMapping As New FunctionMapping(GetType(String), "Concat", 2, "({0} + {1})")
To fill a
FunctionMappingStore, it's simply a matter of calling the
Add method on the
FunctionMappingStore instance to add a
FunctionMapping to the
FunctionMappingStore. Once a
FunctionMappingStore is filled with new mappings, it can be used in all your Linq queries in a thread-safe way, as the store is used on a read-only basis. It's recommended that you build your custom
FunctionMappingStore at the start of your application, and after that re-use it in your queries.
Property get calls are mapped with a method name similar to: get_
propertyname. An operator overload method is mapped with a method name similar to op_
operatorMethod. For the number of parameters, specify 0 for a property getter and 1 for a property which is an indexer. Please see the
LLBLGen Pro Runtime Libraries sourcecode for examples of the function mappings defined in the various DQE projects. Function mappings inside DQEs are defined in the DynamicQueryEngine.cs files.
Calling unmapped .NET methods in a query
If you call a .NET method in your query and no
FunctionMapping can be found for that method, the method is considered to be in-memory and is threated as a normal in-memory method call, as described in the section
Calling an in-memory method in the projection.
Passing a custom FunctionMappingStore to the query
A custom
FunctionMappingStore can be passed to the query by passing them to the constructor of the
LinqMetaData class which is then used in the query. You can also set the custom
FunctionMappingStore by setting the
CustomFunctionMappings property of the
LinqMetaData instance.
If you want to switch custom function mappings between queries created with the same
LinqMetaData instance, you can: the properties called on the
LinqMetaData
(e.g. metaData.Customer) which produce the Queryable elements, grab the custom
FunctionMappingStore instance set at that point. So if you set
CustomFunctionMappings
to null / Nothing in between the creation of queries using the same
LinqMetaData
instance, the query created after
CustomFunctionMappings was set to null on the LinqMetaData instance won't use the
CustomFunctionMappings.
Example of custom FunctionMapping usage
The following example shows how to define a
custom mapping of a new .NET method onto a custom database function and how to use it in a query. As the .NET method is mapped onto a database element, we can use it inside the query and we're not limited to have it inside the projection. The example uses it inside the projection, but you can use it in a Where clause as well for example, as the method due to the mapping converts to a SQL fragment.
SQL function:
-- SQL function stored in the target db.
-- It calculates the order total of a given order.
ALTER FUNCTION fn_CalculateOrderTotal(@orderID int, @useDiscounts bit)
RETURNS DECIMAL
AS
BEGIN
DECLARE @toReturn DECIMAL
SELECT @toReturn = SUM((UnitPrice-(Discount * @useDiscounts)) * Quantity)
FROM [Order Details]
WHERE OrderID = @orderID
GROUP BY OrderID
RETURN @toReturn
END
.NET code using the function:
/// Class which is used to specify the call to the database function. We'll map
/// the function inside it, CalculateOrderTotal, to the database function.
public class NorthwindFunctions
{
public static decimal CalculateOrderTotal(int orderId, bool useDiscounts)
{
// empty body, as it's just here to make the query compile. The call is converted to a SQL function.
return 0.0M;
}
}
/// Class which defines the custom mapping between NorthwindFunctions.CalculateOrderTotal and
/// fn_CalculateOrderTotal, the database function.
public class NorthwindFunctionMappings : FunctionMappingStore
{
public NorthwindFunctionMappings() : base()
{
// define the mapping. SQLServer 2000 needs the schema to be present for the function call,
// so we specify that as well.
this.Add(new FunctionMapping(typeof(NorthwindFunctions), "CalculateOrderTotal", 2,
"fn_CalculateOrderTotal({0}, {1})", "Northwind", "dbo"));
}
}
//...
// these constructs are now allowing the developer to actually call fn_CalculateOrderTotal from within
// the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to
// the LinqMetaData first:
metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the orders with the total calculated by the function inside the database
var q = from o in metaData.Order
where o.CustomerId == "CHOPS"
select new { o.OrderId, OrderTotal = NorthwindFunctions.CalculateOrderTotal(o.OrderId, true) };
''' Class which is used to specify the call to the database function. We'll map
''' the function inside it, CalculateOrderTotal, to the database function.
Public Class NorthwindFunctions
Public Shared Function CalculateOrderTotal(orderId As Integer, useDiscounts As Boolean) As Decimal
' empty body, as it's just here to make the query compile. The call is converted to a SQL function.
Return CDec(0.0)
End Function
End Class
''' Class which defines the custom mapping between NorthwindFunctions.CalculateOrderTotal and
''' fn_CalculateOrderTotal, the database function.
Public Class NorthwindFunctionMappings
Inherits FunctionMappingStore
Public Sub New()
' define the mapping. SQLServer 2000 needs the schema to be present for the function call,
' so we specify that as well.
Me.Add(New FunctionMapping(GetType(NorthwindFunctions), "CalculateOrderTotal", 2, _
"fn_CalculateOrderTotal({0}, {1})", "Northwind", "dbo"))
End Sub
End Class
' ...
' these constructs are now allowing the developer to actually call fn_CalculateOrderTotal from within
' the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to
' the LinqMetaData first:
metaData.CustomFunctionMappings = New NorthwindFunctionMappings()
' fetch the orders with the total calculated by the function inside the database
Dim q = From o In metaData.Order _
Where o.CustomerId = "CHOPS" _
Select New With _
{ _
.OrderId = o.OrderId, _
.OrderTotal = NorthwindFunctions.CalculateOrderTotal(o.OrderId, True) _
}
Full-text search
Using custom FunctionMapping instances, it's possible to define the Full-text search methods required in the query. We opted for this route instead of adding hard-coded codepaths for Full-text search, as for every database full text search is implemented differently.
On SqlServer, we can define a simple method in .NET code to be able to specify the method call, and a mapping to CONTAINS(). We then can use the .NET method to specify a full text search:
/// Class which is used to specify the call to the CONTAINS construct.
public class NorthwindFunctions
{
public static bool FullTextSearch(string fieldToSearch, string toFind)
{
// empty body, as it's just here to make the query compile. The call is converted to a SQL function.
return true;
}
}
/// Class which defines the custom mapping between NorthwindFunctions.FullTextSearch and CONTAINS()
public class NorthwindFunctionMappings : FunctionMappingStore
{
public NorthwindFunctionMappings() : base()
{
// FullTextSearch(2) on 1 field
this.Add(new FunctionMapping(typeof(NorthwindFunctions), "FullTextSearch", 2, "CONTAINS({0}, {1})"));
}
}
//...
// these constructs are now allowing the developer to actually call CONTAINS from within
// the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to
// the LinqMetaData first:
metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the employees which have 'BA' in their Notes field which is Full text search enabled.
var q = from e in metaData.Employee
where NorthwindFunctions.FullTextSearch(e.Notes, "BA")
select e;
''' Class which is used to specify the call to the Contains construct
Public Class NorthwindFunctions
Public Shared Function FullTextSearch(fieldToSearch As String, toFind As String) As Boolean
' empty body, as it's just here to make the query compile. The call is converted to a SQL function.
Return True
End Function
End Class
''' Class which defines the custom mapping between NorthwindFunctions.FullTextSearch and CONTAINS
Public Class NorthwindFunctionMappings
Inherits FunctionMappingStore
Public Sub New()
' FullTextSearch(2) on 1 field
Me.Add(New FunctionMapping(GetType(NorthwindFunctions), "FullTextSearch", 2, "CONTAINS({0}, {1})"))
End Sub
End Class
' ...
' these constructs are now allowing the developer to actually call CONTAINS from within
' the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to
' the LinqMetaData first:
metaData.CustomFunctionMappings = New NorthwindFunctionMappings()
// fetch the employees which have 'BA' in their Notes field which is Full text search enabled.
Dim q = From e In metaData.Employee _
Where NorthwindFunctions.FullTextSearch(e.Notes, "BA") _
Select e
Supported default method / property mappings to functions
The following method/property mappings are supported in SQL. They're grouped per .NET type which defines the method/property and then the list of methods/properties which are supported. If there's no mapping defined for a given database for a given method/property, it's specified with the method or property. The classes mentioned have more methods / properties than the list shows: if a method or property isn't mentioned in the list, there's no mapping of that method or property to a database construct in any supported database. Most of the time, this is because there's no database function equivalent, it needs a .NET object to function properly (e.g. a comparer) etc.
With 'supported' is meant: usable in all areas of a Linq query. A method or property which isn't mentioned in the list is still supported as an in-memory method/property, but solely in the last projection as it otherwise has to be ran on the database.
All returned indexes are floored to 0-based indexes. So if a database function starts with 1 for the first element in a set, 0 is returned if the function returns as index 1. The types of the arguments of the overload supported are given between brackets.
Array methods / properties defined by System.Array
The following Array method / properties are supported.
Boolean methods / properties defined by System.Boolean
Boolean doesn't really have any of its methods/properties mapped, though it has a special method mapped which is actually an operator:
Negate. This mapping creates a NOT on the boolean.
Char methods / properties defined by System.Char
Char doesn't really have any of its methods/properties mapped, though it has a special method mapped which is used to convert the char inside the database to a unicode number:
ToUnicode. The usage of this method is to convert a char to an int. This is required as char constants are stored as integers inside .NET programs and comparing a char value in the database with a constant will result in a compare with an integer, which is then passed to the database as-is, thus as an integer. To avoid that the compare fails, the char value is converted to unicode inside the database using the function mapped as Char.ToUnicode(1).
The integer value isn't converted to char, because the expression tree contains a convert from char to int, so the only way to handle this in general is to convert that conversion at that spot with the parameters at hand.
Not on: Firebird
Convert methods / properties defined by System.Convert
The Convert class is a class which can convert elements from one type to the other. The following methods and properties are supported. Only the overloads which accept a single value are supported. If one needs specific formatting, please create a custom mapping. Conversions to Char use the database default's length. This could lead to trailing spaces. Conversions to String use the maximum length of unicode variable strings, e.g. nvarchar(4000).
- ToBoolean.
- ToByte. Not on: IBM DB2, Firebird
- ToChar
- ToDateTime
- ToDecimal
- ToDouble.
- ToInt16
- ToInt32
- ToInt64. Not on: MS Access
- ToSingle
- ToString
DateTime methods/properties, defined by System.DateTime
The following DateTime functions are supported. The fraction part in the double typed value passed into the Add* methods is discarded in most databases. Passing 1.4 for example will add a single day when calling AddDays(1.4).
The property 'Now' isn't mapped, although it could be mapped onto a database function easily. The problem is that with 'Now', it's unclear if the
client date is meant or the
server date. If the property would be mapped onto a server function, the
server date/time would be used in the query, while if it wouldn't get a mapping, the
client date/time would be used. If one wants a
server date compare, create an extension method for DateTime and map that method in a custom mapping to the database function to retrieve the current date, e.g. GETDATE() on sqlserver.
- AddDays(double). Not on: Firebird
- AddHours(double) . Not on: Firebird
- AddMilliseconds(double). Not on: MS Access, Firebird, Oracle
- AddMinutes(double) . Not on: Firebird
- AddMonths(double) . Not on: Firebird
- AddYears(double) . Not on: Firebird
- Compare(DateTime, DateTime)
The following properties are supported
- Date
- Day. Not on: Firebird
- DayOfWeek. Not on: Firebird
- DayOfYear. Not on: Firebird
- Hour. Not on: Firebird
- Millisecond. Not on: MS Access, Firebird, Oracle, PostgreSql
- Minute. Not on: Firebird
- Month. Not on: Firebird
- Second. Not on: Firebird
- Year. Not on: Firebird
Decimal methods/properties, defined by System.Decimal
The following Decimal methods/properties are supported. The various To
type methods aren't supported, though one could use the Convert.
method mappings instead.
- Add
- Ceiling. Not on: Firebird
- Compare
- Divide
- Floor. Not on: Firebird
- Multiply
- Negate
- Remainder. Not on: MS Access
- Round(Decimal) and Round(Decimal, Int32) . Not on: Firebird
- Substract
- Truncate. Not on: Firebird
String methods/properties, defined by System.String
The following string methods/properties are supported. Most of them don't work on ntext/text/clob fields due to the use of LEN/REVERSE etc. functions. Often, the database equivalents use a function which determines the length of the string. This is often done by LEN() or similar function. These functions in general don't report trailing blanks, so LEN('Foo ') will be '3', not 5.
- Concat(string, string), or +/& operators.
- Compare(string, string)
- IndexOf(char) . Not on Firebird
- IndexOf(string) . Not on Firebird
- IndexOf(char, int) . Not on Firebird, PostgreSql
- IndexOf(string, int) . Not on Firebird, PostgreSql
- LastIndexOf(char). Not on: IBM DB2, Firebird
- LastIndexOf(string) . Not on: IBM DB2, Firebird
- LastIndexOf(char, int). Not on: IBM DB2, Firebird, Oracle
- LastIndexOf(string, int) . Not on: IBM DB2, Firebird, Oracle
- PadLeft(int) . Not on Firebird
- PadRight(int) . Not on Firebird
- Remove(int) . Not on Firebird
- Remove(int, int) . Not on Firebird
- Replace(string, string) . Not on Firebird
- Replace(char, char) . Not on Firebird
- Substring(int) . Not on Firebird
- Substring(int, int) . Not on Firebird
- ToLower()
- ToUpper()
- Trim
The following properties are supported
- Length
- Chars ( Indexer). Not on: Firebird
Object method/properties, defined by System.Object
The Object properties/methods aren't really defined as methods/properties of object. For example 'ToString()' is overloaded in a lot of classes. ToString is converted in all classes as a call to Convert.ToString(value).
The following
artificial methods are mapped on Object. These methods are used by the framework to produce a proper operation. If you want to overrule these functions, you have to add mappings of these functions to object in your custom mappings.
- IIF. This method mapping is used to produce the CASE statement for IIF / ?: statements in the projection or elsewhere.
- IIF_Bool. Similar to IIF, but it now works with boolean values. This requires a comparison with the same values, so the result is indeed a boolean.
- LeftShift. This method mapping is used to perform a leftshift of an operand a by the # of bits specified by operand b. Not on Firebird.
- RightShift. This method mapping is used to perform a rightshift of an operand a by the # of bits specified by operand b. Not on Firebird
- BooleanInProjectionWrapper. This method mapping is used to wrap a boolean resulting expression in a projection via a DbFunctionCall. This is done in general through a CASE block. The convention is that this block has to produce a positive integer value if it represents a true value, 0 otherwise.
Math method / properties, defined by System.Math
The following Math methods are supported. We only map 'Pow' as the VB.NET language has a Power operator: ^ and therefore we need to map this function. If a developer needs more functions mapped, s/he should create a custom mapping.
Remarks and limitations
The following remarks and limitations have to be taken into account when using function mappings.
- Functions only work on the .NET type's lookalike type in the database. This means that if you use a string function in the Linq query on a field which is of type string but in the database it's an int field and you use a type converter to convert back/forth to int from the string, the function will fail as the provider will assume the type in the DB can work with a string function. In this case, it's
advised to write a custom function mapping.
- If you use your own referenced typed type in the query with an operator you've overloaded in your type, the query will likely fail due to the fact that there's no mapping for the op_operatorMethod. If you run into this error, provide a custom mapping for this function. This will mean the function will run in the database.
- We tried to add as much useful function mappings as possible, however it might be that the system function you want to use isn't mapped. In that case, add a custom mapping. With thousands of system functions defined in systems like Oracle or DB2, it's undoable to ship a mapping for every one of them.
- It might be that not all mappings work on your database system. E.g. a mapping to a function in
SQL Server 2005 might not work on SQL Server 7. This should be rare, but nevertheless it sometimes happens. If you experience an exception at runtime, enable DQE tracing and examine the query to see if the mapping you're using is indeed supported on the version of your database system.
- Mixing method calls to methods which run in-memory with database elements only works if the method or operator (e.g. '+') is run in memory and the operands are database elements. So mixing method calls which have a database mapping with methods which don't have a mapping isn't going to work, as the method which has a database mapping requires an operand which value isn't known till the query has run as it relies on in-memory method calls.
- If a method doesn't have a mapping, it still can be used in a linq
query, though only in the last projection. This means that if you use a
method which doesn't have a mapping in a Where clause or in a projection
which will be wrapped in another query, the method won't be able to be
translated to SQL and therefore will fail. See the example below:
DaysInMonth isn't a mapped function.
// query which will work
var q = from o in metaData.Order
select DateTime.DaysInMonth(o.OrderDate.Year, o.OrderDate.Month);
// query which fails, because the projection is wrapped inside another query
var q = (from o in metaData.Order
select DateTime.DaysInMonth(o.OrderDate.Year, o.OrderDate.Month)).Count();
' query which will work
Dim q = From o In metaData.Order _
Select DateTime.DaysInMonth(o.OrderDate.Year, o.OrderDate.Month)
' query which fails, because the projection is wrapped inside another query
Dim q = (From o In metaData.Order _
Select DateTime.DaysInMonth(o.OrderDate.Year, o.OrderDate.Month)).Count()